package com.file.excel;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.exception.ApiException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;

/**
 * @author cxt
 * @date 2020/7/21
 * 读取Excel文件中的值
 * <p>
 * 导入jar包
 * <dependency>
 * <groupId>org.apache.poi</groupId>
 * <artifactId>poi-ooxml</artifactId>
 * <version>3.17</version>
 * </dependency>
 */
public class ExcelDataUtil {

    public static void main(String[] args) {
        ExcelReader reader = ExcelUtil.getReader(new File("D:\\培训项目小类(反馈).xls"), "sheet1");
        List<Map<String, Object>> maps = reader.readAll();
        ArrayList<String> result = new ArrayList<>();

        HashMap<String, String> hashMap1 = new HashMap<>();
        HashMap<String, String> hashMap2 = new HashMap<>();
        HashMap<String, String> hashMap3 = new HashMap<>();
        HashMap<String, String> hashMap4 = new HashMap<>();
        for (Map<String, Object> map : maps) {
            String one = (String) map.get("one");
            String code1 = hashMap1.get(one);
            if (code1 == null) {
                // 添加到map
                int size = hashMap1.size();
                if (size == 0) {
                    size = 1;
                }
                code1 = size * 1000 + "";
                hashMap1.put(one, code1);

                String str = "INSERT INTO cultivate_type (code, name, pid, created_by, created_date, last_modified_by, last_modified_date) VALUES('" + code1 + "', '" + one + "', 0, '18993536174', '2022-09-14 17:02:31.0', '18993536174', '2022-09-14 17:02:31.0');\n";
                result.add(str);
            }

            String two = (String) map.get("two");
            String code2 = hashMap2.get(two);
            if (code2 == null) {
                // 添加到map
                int size = hashMap2.size();
                if (size == 0) {
                    size = 1;
                }
                code2 = size * 1000 + "";
                hashMap2.put(one, code2);

                String str = "INSERT INTO cultivate_type (code, name, pid, created_by, created_date, last_modified_by, last_modified_date) VALUES('" + code2 + "', '" + two + "', '" + code1 + "', '18993536174', '2022-09-14 17:02:31.0', '18993536174', '2022-09-14 17:02:31.0');\n";
                result.add(str);
            }

            String three = (String) map.get("three");
            String code3 = hashMap3.get(three);
            if (code3 == null) {
                // 添加到map
                int size = hashMap3.size();
                if (size == 0) {
                    size = 1;
                }
                code3 = size * 1000 + "";
                hashMap3.put(one, code3);

                String str = "INSERT INTO cultivate_type (code, name, pid, created_by, created_date, last_modified_by, last_modified_date) VALUES('" + code3 + "', '" + three + "', '" + code2 + "', '18993536174', '2022-09-14 17:02:31.0', '18993536174', '2022-09-14 17:02:31.0');\n";
                result.add(str);
            }

            String five = (String) map.get("four");
            String code4 = hashMap4.get(five);
            if (code4 == null) {
                // 添加到map
                int size = hashMap4.size();
                if (size == 0) {
                    size = 1;
                }
                code4 = size * 1000 + "";
                hashMap4.put(one, code4);

                String str = "INSERT INTO cultivate_type (code, name, pid, created_by, created_date, last_modified_by, last_modified_date) VALUES('" + code4 + "', '" + five + "', '" + code3 + "', '18993536174', '2022-09-14 17:02:31.0', '18993536174', '2022-09-14 17:02:31.0');\n";
                result.add(str);
            }
        }

        result.forEach(System.out::println);


//        try {
//            List<Map<String, String>> maps = redExcel("F:\\设备巡检.xls");
//            maps.forEach(System.out::println);
//        }catch (Exception e){
//            System.out.println(e);
//        }
    }

    /**
     * 读取excel内容
     * <p>
     * 用户模式下：
     * 弊端：对于少量的数据可以，单数对于大量的数据，会造成内存占据过大，有时候会造成内存溢出
     * 建议修改成事件模式
     */
    public static List<Map<String, String>> redExcel(String filePath) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new ApiException(ApiException.F_FILE_EXIST);
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);
        ArrayList<Map<String, String>> list = new ArrayList<>();

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 1; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            LinkedHashMap<String, String> map = new LinkedHashMap<>();
            // 读取每一格内容
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);
                // cell.setCellType(XSSFCell.CELL_TYPE_STRING); 过期，使用下面替换
                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                map.put(getString(titleCell), getString(cell));
            }
            if (map.isEmpty()) {
                continue;
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }
}


